package com.jld.managerialsystemforcovid19.controller;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.jld.managerialsystemforcovid19.dto.Covid19DataDto;
import com.jld.managerialsystemforcovid19.entity.Covid19Data;
import com.jld.managerialsystemforcovid19.service.IndexService;
import com.jld.managerialsystemforcovid19.vo.DataView;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

@Controller
public class ChinaDataAdminController {
    @Autowired
    private IndexService indexService;

    //跳转页面
    @RequestMapping("/toChinaManager")
    public String toChinaManager() {
        return "admin/chinadatamanger";
    }

    /**
     * 模糊查询及其分页
     */
    @RequestMapping("/listDataByPage")
    @ResponseBody
    public DataView listDataByPage(Covid19DataDto covid19DataDto) {
        //创建分页对象，当前页码，限制每页显示多少条数据
        Page<Covid19Data> page = new Page<>(covid19DataDto.getPage(), covid19DataDto.getLimit());
        //添加模糊查询条件
        LambdaQueryWrapper<Covid19Data> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.like(covid19DataDto.getName() != null, Covid19Data::getName, covid19DataDto.getName());
        //疫情数据确诊最多人数排在最上面
        lambdaQueryWrapper.orderByDesc(Covid19Data::getValue);
        //查询数据库
        indexService.page(page, lambdaQueryWrapper);
        //返回封装数据
        DataView dataView = new DataView(page.getTotal(), page.getRecords());
        return dataView;
    }

    /**
     * 根据id删除某一条省份数据
     *
     * @return
     */
    @RequestMapping("/china/deleteById")
    @ResponseBody
    public DataView deleteById(Integer id) {
        indexService.removeById(id);
        DataView dataView = new DataView();
        dataView.setCode(200);
        dataView.setMsg("数据删除成功！");
        return dataView;
    }

    /**
     * 新增及修改
     * 根据id进行新增或者修改。有id修改，无id则进行新增
     */
    @RequestMapping("/china/addOrUpdateChina")
    @ResponseBody
    public DataView addOrUpdateChina(Covid19Data covid19Data) {
        boolean save = indexService.saveOrUpdate(covid19Data);
        DataView dataView = new DataView();
        if (save) {
            dataView.setCode(200);
            dataView.setMsg("新增数据成功");
            return dataView;
        }
        dataView.setCode(100);
        dataView.setMsg("失败：未能添加数据！");
        return dataView;
    }

    /**
     * excel的拖拽或点击上传
     * 1.前台页面发送请求，上传文件MultipartFile Http
     * 2.Controller上传文件参数
     * 3.POI解析文件，将里面数据一行一行全部解析
     * 4.将每一行数据插入至数据库
     */
    @RequestMapping("/excelImportChina")
    @ResponseBody
    public DataView excelImportChina(@RequestParam("file") MultipartFile file) throws IOException {
        DataView dataView = new DataView();
        //1.文件不为空
        if (file.isEmpty()) {
            dataView.setMsg("文件为空。上传失败");
        }
        //2.POI获取excel解析数据
        XSSFWorkbook sheets = new XSSFWorkbook(file.getInputStream());
        XSSFSheet sheet = sheets.getSheetAt(0);//获取excel表的一个sheet  根据索引下标

        //3.定义一个集合，接收文件中的数据
        List<Covid19Data> list = new ArrayList<>();
        //4.定义每一行数据的变量
        XSSFRow row = null;
        //5.解析数据装到集合中
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            //a.定义Covid19Data类的一个实例,方便将每行数据装到实例中
            Covid19Data covid19Data = new Covid19Data();
            row = sheet.getRow(i);//将sheet表中每一行数据获取到
            //b.解析数据
            /*通过每一行row拿到索引下标为0列（第一列）的每一个String类型单元格cell，解析出城市名称，继而赋值给实例对象covid19Data*/
            covid19Data.setName(row.getCell(0).getStringCellValue());
            covid19Data.setValue((int) row.getCell(1).getNumericCellValue());//解析成城市疫情数据
            //c.将实例对象的每条数据装到集合
            list.add(covid19Data);
        }
        //6.将集合数据插入数据库
        indexService.saveBatch(list);

        dataView.setCode(200);
        dataView.setMsg("Excel表插入成功");
        return dataView;

    }


    /**
     * 导出Excel数据（中国疫情数据）
     * 1.查询数据库
     * 2.建立Excel对象，封装数据
     * 3.建立输出流，输出文件
     */
    @RequestMapping("excelOutPortChina")
    @ResponseBody
    public void excelOutPortChina(HttpServletResponse response) {
        //1.查询数据库
        List<Covid19Data> list = indexService.list();

        //2.建立Excel对象，封装数据
        response.setCharacterEncoding("UTF-8");
        //2.1新建Excel对象工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //2.2创建sheet对象
        XSSFSheet sheet = workbook.createSheet("中国疫情数据表");
        //2.3创建表头
        XSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("城市名称");
        row.createCell(1).setCellValue("确诊人数");

        //3.遍历数据，封装Excel对象
        for (Covid19Data covid19Data : list) {
            XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            dataRow.createCell(0).setCellValue(covid19Data.getName());//每一行第一列获取城市名称
            dataRow.createCell(1).setCellValue(covid19Data.getValue());//每一行第二列获取确诊人数
        }

        //4.建立输出流，输出浏览器文件
        OutputStream os = null;
        //4.1设置excel名字
        try {
            //6-设置Excel的名称
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String("中国疫情数据表".getBytes(), "iso-8859-1") + ".xls");
            os = response.getOutputStream();
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            try {
                if (os != null) {
                    os.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}
